# coding=utf-8 ##��utf-8���봢�������ַ�
'''
Created on 2010-11-16
Us
@author: Q
'''

"""
It will search across the database, and check whether is latest
"""
import os,sqlite3,time,string,sys
from Configuration import Configuration

cf = Configuration()

def createTable():
    os.system("rm DB")
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    c = conn.cursor()
    c.execute('''create table Experiments (expId int, expName text, turn int, 
    type int, factor int, starttime double, endtime double, cputime double, year int, gameversion text, filename text)''')
    conn.commit()
    #创建表格

type = 0
def getType():
    return type

def getGameVersion():
    return "1.04"

def update(expId, expName, turn, factor, starttime, endtime, cputime, filename):
    if cputime >= 0:
        cmd = "update Experiments set cputime = %f"%cputime + " where expId = %d and expName = '"%expId + expName + "' and turn = %d and factor = %d"%(turn, factor)  
        execute(cmd);
#        year = getYear(expId, expName, turn, factor)
#        remove(expId, expName, turn, factor)
#        insert(expId, expName, turn, factor, starttime, endtime, cputime, year, filename)

def execute(cmd):
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    c = conn.cursor()
    type = getType()
    print cmd
    c.execute(cmd)
    conn.commit()

def insert(expId, expName, turn, factor, starttime, endtime, cputime, year, filename):
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    c = conn.cursor()
    # 插入数据，执行SQL语句
    gameversion = getGameVersion()
    type = getType()
    if year == (None,): year = 0
    tuple = (expId, expName, turn, type, factor, starttime, endtime, cputime, year, gameversion, filename)
#    print tuple
    c.execute("insert into Experiments values (?,?,?,?,?,?,?,?,?,?,?)", tuple)
    #将变动保存到数据库文件，如果没有执行词语句，则前面的insert 语句操作不会被保存
    conn.commit()
    #得到所有的记录
    return True

def remove(expId, expName, turn, factor):
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    #链接数据库文件
    #如果数据库文件不存在，回新建一个，如果存在则打开此文件
    c = conn.cursor()
    # 插入数据，执行SQL语句
    result = c.execute("delete from Experiments where expId = ? and expName = ? and turn = ? and factor = ?", (expId, expName, turn, factor))  
    #将变动保存到数据库文件，如果没有执行词语句，则前面的insert 语句操作不会被保存
    conn.commit()

def getTime(expId, expName, turn , factor):
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    c = conn.cursor()
    result = c.execute("select cputime from Experiments where expId = ? and expName = ? and turn = ? and factor = ?", (expId, expName, turn, factor))
    conn.commit()
    lines = result.fetchall();
    return lines

def getAllTime(expId, expName):
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    c = conn.cursor()
    result = c.execute("select turn, factor, cputime, year, expId from Experiments where expId = ? and expName = ?", (expId, expName))
    conn.commit()
    lines = result.fetchall();
    return lines

def show():
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    c = conn.cursor()
    # 插入数据，执行SQL语句
    result = c.execute("select * from Experiments order by factor")  
    #将变动保存到数据库文件，如果没有执行词语句，则前面的insert 语句操作不会被保存
    conn.commit()
    #得到所有的记录
    lines = result.fetchall();
    if len(lines) ==0 :
        return True
    for line in lines:
        print line

def showtime(id):
    path = os.path.join("DB")
    conn = sqlite3.connect(path)
    #链接数据库文件
    #如果数据库文件不存在，回新建一个，如果存在则打开此文件
    c = conn.cursor()
    # 插入数据，执行SQL语句
    result = c.execute("select * from Experiments where expId = ? order by factor", (id,))  
    #将变动保存到数据库文件，如果没有执行词语句，则前面的insert 语句操作不会被保存
    conn.commit()
    #得到所有的记录
    lines = result.fetchall();
    if len(lines) ==0 :
        return True
    for line in lines:
        print line[4], line[7]
        
def getYear(expId, expName, turn, factor):
    ret = 0
    tuple = (expId, expName, turn, factor)
    command = "select year from Experiments where expId = ? and expName = ? and turn = ? and factor = ?"
    path = os.path.join("..", "test_result", "DB")
    conn = sqlite3.connect(path)
    #链接数据库文件
    #如果数据库文件不存在，回新建一个，如果存在则打开此文件
    c = conn.cursor()
    # 插入数据，执行SQL语句
    result = c.execute(command, tuple)
    lines = result.fetchall();
    if len(lines) ==0 :
        ret = 0
    else : ret = lines[0][0]
    return ret
    
def getInfo(expId, expName, turn, type, factor, gameversion, year):
    #where name_last=? and age=?"
    tuple = (expId, expName, turn, type, factor, gameversion, year)
    command = "select starttime, endtime, cputime, filename from Experiments where expId = ? and expName = ? and turn = ? and type = ? and factor = ? and gameversion = ? and year = ?"
    path = os.path.join("..", "test_result", "DB")
    conn = sqlite3.connect(path)
    #链接数据库文件
    #如果数据库文件不存在，回新建一个，如果存在则打开此文件
    c = conn.cursor()
    # 插入数据，执行SQL语句
    result = c.execute(command, tuple)
    lines = result.fetchall();
    if len(lines) ==0 :
        return None
    return lines[0]
def rebuildDataBase(path=cf.getPath()):
    data = []
    error = 0
    for filename in os.listdir(path):
        if os.path.isdir(os.path.join(path,filename)):
            ret = rebuildDataBase(os.path.join(path,filename))
            data.extend(ret[0])
            error = error + ret[1]
        elif filename.endswith("log.txt"):
            try:
                file = open(os.path.join(path,filename))
                lines = file.readlines()
                if len(lines) == 0:
                    error = error +1
                totalYear = len(lines) - 7
                starttime = string.atof(lines[2])
                endtime = string.atof(lines[4 + totalYear])
                purefilename = os.path.basename(filename)
                tuple = string.split(purefilename, "_")
                l = len(tuple)
                expId = string.atoi(tuple[0])
                factor = -1
                expName = ""
#                print filename
                for i in range(0, l):
                    if tuple[i].find("node") >=0:
                        factor = string.atoi(tuple[i-1])
                        for j in range(1, i-1):
                            expName = expName + tuple[j]
#                print expId, expName, factor, starttime, endtime
                insert(expId, expName, 0, factor, starttime, endtime, 0, totalYear, os.path.join(filename))
            except:
                print os.path.join(path, filename), len(lines)
                pass
    print "total error file", error
    return data,error

#def insert(expId, expName, turn, type, factor, starttime, endtime, cputime, gameversion, filename):
if __name__ == '__main__':
    i = 1
#    createTable()
#    rebuildDataBase()  

#    insert("1", "1a1", 1, 0, 1, time.time(), time.time(), time.time(), "1.04", "1_1a1_1_1" )
#    show()
#    remove()
